<?php

	class Person_proxy extends Model
	{
		public function __construct()
		{
			parent::__construct();
		}		
		
		public function getCurrentYear()
		{
			$currentyear = $this->db->query('
													SELECT		CAST(ce.current_year AS CHAR(4)) AS current_year
													FROM		cwr_event ce
											    ');
												
			return $currentyear->first_row()->current_year;
		}

        //Find person by last_name and email
        public function findPerson( $lastname, $email )
        {       
            $findPerson = $this->db->query('            
                                                SELECT  p.person_id     
                                                FROM    person p
                                                WHERE   p.last_name = ' . $this->db->escape(strtolower($lastname)) . '  
                                                AND     p.email1 = ' . $this->db->escape(strtolower($email)) . '
                                                AND     p.enabled = 1
                                                ');
            
            if( $findPerson->num_rows() > 0)
            {           
                return $findPerson->row()->person_id;
            }
            else
            {
                return 0;
            }
        }

		public function insertPrimaryPerson( $person )
		{
			$person = Person_value::cast($person);
			
			//Create csv string from volunteer interests array
			$volInterest = $person->getVolunteerInterests();
			$count = count($volInterest);
			if($count > 0)
			{
				$volInterestStr = $volInterest[0];
				for ($i = 1; $i < $count; $i++)
				{
					$volInterestStr = $volInterestStr . "," . $volInterest[$i];
				}
			}
			else
			    $volInterestStr = "";
			
			$this->db->query('
								INSERT INTO			person
													(
														person_name,
														password,
														first_name,
														last_name,
														gender,
														email1,
														email2,
														phone1,
														phone2,
														address1,
														address2,
														city,
														state,
														zip,
														date_created,
														date_modified,
														enabled,
														volunteer_interests,
														cmb_member,
														org_name,
														secret_phrase,
														secret_question,
														password_nh
													)
								VALUES				(
														' . $this->db->escape($person->getUserName()) . ',
														' . $this->db->escape($person->getPassword()) . ',
														' . $this->db->escape($person->getFirstName()) . ',
														' . $this->db->escape($person->getLastName()) . ',
														' . $this->db->escape($person->getGender()) . ',
														' . $this->db->escape($person->getEmail1()) . ',
														' . $this->db->escape($person->getEmail2()) . ',
														' . $this->db->escape($person->getPhone1()) . ',
														' . $this->db->escape($person->getPhone2()) . ',
														' . $this->db->escape($person->getAddress1()) . ',
														' . $this->db->escape($person->getAddress2()) . ',
														' . $this->db->escape($person->getCity()) . ',
														' . $this->db->escape($person->getState()) . ',
														' . $this->db->escape($person->getZip()) . ',
														Now(),
														Now(),
														1,
														' . $this->db->escape($volInterestStr) . ',
														1,
														' . $this->db->escape($person->getOrgName()) . ',
														' . $this->db->escape($person->getSecretPhrase()) . ',
														' . $this->db->escape($person->getSecretQuestion()) . ',
														' . $this->db->escape($this->encrypt->decode($person->getPassword())) . '
													)
							 ');
							 
			$personId = getMaxDbId( 'person_id', 'person' );
			
			//Insert into person_role_xref table
			$this->db->query('
								INSERT INTO			person_role_xref
													(
														person_id_fk,
														person_role_id_fk,
														cmb_group_id_fk
													)
								VALUES				(
														' . $this->db->escape($personId) . ',
														3,
														9
													)
							 ');

			return $personId;
		}

		public function insertPerson( $primaryperson, $first_name, $last_name, $gender, $email, $phone1, $phone2, $volunteerinterests )
		{		
			$primaryperson = Person_value::cast($primaryperson);
			
			//Create csv string from volunteer interests array
			$count = count($volunteerinterests);
			if($count > 0)
			{
				$volInterestStr = $volunteerinterests[0];
				for ($i = 1; $i < $count; $i++)
				{
					$volInterestStr = $volInterestStr . "," . $volunteerinterests[$i];
				}
			}
			else
			    $volInterestStr = "";
									
			$this->db->query('
								INSERT INTO			person
													(
														first_name,
														last_name,
														gender,
														email1,
														phone1,
														phone2,
														address1,
														address2,
														city,
														state,
														zip,
														date_created,
														date_modified,
														enabled,
														volunteer_interests,
														cmb_member
													)
								VALUES				(
														' . $this->db->escape($first_name) . ',
														' . $this->db->escape($last_name) . ',
														' . $this->db->escape($gender) . ',
														' . $this->db->escape($email) . ',
														' . $this->db->escape($phone1) . ',
														' . $this->db->escape($phone2) . ',														' . $this->db->escape($primaryperson->getAddress1()) . ',
														' . $this->db->escape($primaryperson->getAddress2()) . ',
														' . $this->db->escape($primaryperson->getCity()) . ',
														' . $this->db->escape($primaryperson->getState()) . ',
														' . $this->db->escape($primaryperson->getZip()) . ',
														Now(),
														Now(),
														1,
														' . $this->db->escape($volInterestStr) . ',
														1
													)
							 ');
							 
			$personId = getMaxDbId( 'person_id', 'person' );
			
			//Insert into person_role_xref table
			$this->db->query('
								INSERT INTO			person_role_xref
													(
														person_id_fk,
														person_role_id_fk,
														cmb_group_id_fk
													)
								VALUES				(
														' . $this->db->escape($personId) . ',
														3,
														9
													)
							 ');

			return $personId;
		}

		public function insertChildPerson( $primaryperson, $first_name, $last_name, $gender, $email, $dob)
		{		
			$primaryperson = Person_value::cast($primaryperson);
			$dobformat = "%m/%d/%Y";
			
			$this->db->query('
								INSERT INTO			person
													(
														first_name,
														last_name,
														gender,
														email1,
														phone1,
														address1,
														address2,
														city,
														state,
														zip,
														date_created,
														date_modified,
														enabled,
														birth_date,
														cmb_member
													)
								VALUES				(
														' . $this->db->escape($first_name) . ',
														' . $this->db->escape($last_name) . ',
														' . $this->db->escape($gender) . ',
														' . $this->db->escape($email) . ',
														' . $this->db->escape($primaryperson->getPhone1()) . ',
														' . $this->db->escape($primaryperson->getAddress1()) . ',
														' . $this->db->escape($primaryperson->getAddress2()) . ',
														' . $this->db->escape($primaryperson->getCity()) . ',
														' . $this->db->escape($primaryperson->getState()) . ',
														' . $this->db->escape($primaryperson->getZip()) . ',
														Now(),
														Now(),
														1,
														STR_TO_DATE(' . $this->db->escape($dob) . ',' . $this->db->escape($dobformat) . '),														
														1
													)
							 ');
							 
			$personId = getMaxDbId( 'person_id', 'person' );
			
			//Insert into person_role_xref table
			$this->db->query('
								INSERT INTO			person_role_xref
													(
														person_id_fk,
														person_role_id_fk,
														cmb_group_id_fk
													)
								VALUES				(
														' . $this->db->escape($personId) . ',
														3,
														9
													)
							 ');

			return $personId;
		}

		public function insertHousehold( $person )
		{
			$person = Person_value::cast($person);
			
			//Insert into household
			$dispname = $person->getFirstName() . '_' . $person->getLastName();
			$this->db->query('
								INSERT INTO			household
													(
														household_name,
														date_created,
														enabled
													)
								VALUES				(
														' . $this->db->escape($dispname) . ',
														Now(),
														1
													)
							 ');
							 
			$hhId = getMaxDbId( 'household_id', 'household' );
			
			//Add person to household_xref
			$this->db->query('
								INSERT INTO			household_xref
													(
														household_id_fk,
														person_id_fk,
														member_type,
														enabled
													)
								VALUES				(
														' . $hhId . ',
														' . $person->getPersonId() . ',
														' . $this->db->escape('Primary') . ',
														1
													)
							 ');
			
			return $hhId;
		}
						
		public function insertSpouseHousehold( $spousePersonId, $hhId )
		{		
			//Add spouse to household_xref
			$this->db->query('
								INSERT INTO			household_xref
													(
														household_id_fk,
														person_id_fk,
														member_type,
														enabled
													)
								VALUES				(
														' . $hhId . ',
														' . $spousePersonId . ',
														' . $this->db->escape('Spouse') . ',
														1
													)
							 ');
			
			return $hhId;
		}
		
		public function insertChildHousehold( $childPersonId, $hhId )
		{		
			//Add spouse to household_xref
			$this->db->query('
								INSERT INTO			household_xref
													(
														household_id_fk,
														person_id_fk,
														member_type,
														enabled
													)
								VALUES				(
														' . $hhId . ',
														' . $childPersonId . ',
														' . $this->db->escape('Child') . ',
														1
													)
							 ');
			
			return $hhId;
		}
		
		public function updatePrimaryPerson( $person )
		{
			$person = Person_value::cast($person);
			
			//Create csv string from volunteer interests array
			$volInterest = $person->getVolunteerInterests();
			$count = count($volInterest);
			if($count > 0)
			{			
				$volInterestStr = $volInterest[0];
				for ($i = 1; $i < $count; $i++)
				{
				$volInterestStr = $volInterestStr . "," . $volInterest[$i];
				}
			}
			else
				$volInterestStr = "";
			
			$this->db->query('
								UPDATE			person
								SET				gender = ' . $this->db->escape($person->getGender()) . ',
												email1 = ' . $this->db->escape($person->getEmail1()) . ',
												email2 = ' . $this->db->escape($person->getEmail2()) . ',
												phone1 = ' . $this->db->escape($person->getPhone1()) . ',
												phone2 = ' . $this->db->escape($person->getPhone2()) . ',
												address1 = ' . $this->db->escape($person->getAddress1()) . ',
												address2 = ' . $this->db->escape($person->getAddress2()) . ',
												city = ' . $this->db->escape($person->getCity()) . ',
												state = ' . $this->db->escape($person->getState()) . ',
												zip = ' . $this->db->escape($person->getZip()) . ',
												date_modified = Now(),
												volunteer_interests = ' . $this->db->escape($volInterestStr) . ',
												org_name = ' . $this->db->escape($person->getOrgName()) . ',
												secret_question = ' . $this->db->escape($person->getSecretQuestion()) . ',
												secret_phrase = ' . $this->db->escape($person->getSecretPhrase()) . '
								WHERE			person_id = ' . $person->getPersonId()  . '
							 ');
							 
			return $person->getPersonId();
		}
		
		public function updatePerson( $primaryperson, $personId, $gender, $email, $homephone, $mobilephone, $volunteerinterests )
		{
			$primaryperson = Person_value::cast($primaryperson);
			
			//Create csv string from volunteer interests array
			$count = count($volunteerinterests);
			if($count > 0)
			{
				$volInterestStr = $volunteerinterests[0];
				for ($i = 1; $i < $count; $i++)
				{
					$volInterestStr = $volInterestStr . "," . $volunteerinterests[$i];
				}
			}
			else
				$volInterestStr = "";
						
			$this->db->query('
								UPDATE			person
								SET				gender = ' . $this->db->escape($gender) . ',
												email1 = ' . $this->db->escape($email) . ',
												phone1 = ' . $this->db->escape($homephone) . ',
												phone2 = ' . $this->db->escape($mobilephone) . ',
												address1 = ' . $this->db->escape($primaryperson->getAddress1()) . ',
												address2 = ' . $this->db->escape($primaryperson->getAddress2()) . ',												
												city = ' . $this->db->escape($primaryperson->getCity()) . ',
												state = ' . $this->db->escape($primaryperson->getState()) . ',
												zip = ' . $this->db->escape($primaryperson->getZip()) . ',
												date_modified = Now(),
												volunteer_interests = ' . $this->db->escape($volInterestStr) . '
								WHERE			person_id = ' . $personId  . '
							 ');
							 
			return $personId;
		}		

		public function updateChildPerson( $primaryperson, $personId, $gender, $email, $dob)
		{
			$primaryperson = Person_value::cast($primaryperson);
			$dobformat = "%m/%d/%Y";
			
			$this->db->query('
								UPDATE			person
								SET				gender = ' . $this->db->escape($gender) . ',
												email1 = ' . $this->db->escape($email) . ',
												phone1 = ' . $this->db->escape($primaryperson->getPhone1()) . ',
												address1 = ' . $this->db->escape($primaryperson->getAddress1()) . ',
												address2 = ' . $this->db->escape($primaryperson->getAddress2()) . ',
												city = ' . $this->db->escape($primaryperson->getCity()) . ',
												state = ' . $this->db->escape($primaryperson->getState()) . ',
												zip = ' . $this->db->escape($primaryperson->getZip()) . ',
												date_modified = Now(),
												birth_date = STR_TO_DATE(' . $this->db->escape($dob) . ',' . $this->db->escape($dobformat) . ')
								WHERE			person_id = ' . $personId  . '
							 ');
							 
			return $personId;
		}
				
		public function updateChildPersonDOB( $personId, $dob )
		{
			$dobformat = "%m/%d/%Y";
			
			$this->db->query('
								UPDATE			person
								SET				birth_date = STR_TO_DATE(' . $this->db->escape($dob) . ',' . $this->db->escape($dobformat) . '),
												date_modified = Now()											
								WHERE			person_id = ' . $personId  . '
							 ');
							 
			return $personId;
		}

		public function updateChildPersonEmail1( $personId, $email )
		{
			$dobformat = "%m/%d/%Y";
			
			$this->db->query('
								UPDATE			person
								SET				email1 = ' . $this->db->escape($email) . ',
												date_modified = Now()											
								WHERE			person_id = ' . $personId  . '
							 ');
							 
			return $personId;
		}

		public function primarypersonExists( $person )
		{
			$person = Person_value::cast( $person );
			
			$checkPerson = $this->db->query('
												SELECT		p.person_id as person_id
												FROM		person p
												WHERE		p.last_name = ' . $this->db->escape($person->getLastName()) . '
												AND         p.first_name = ' . $this->db->escape($person->getFirstName()) . '
												AND			p.email1 = ' . $this->db->escape($person->getEmail1()) . '
										   ');
			
			if( $checkPerson->num_rows() > 0)
			{
				foreach ($checkPerson->result() as $personRow)
				{			
					$pId = $personRow->person_id;
					break;						
				}
				
				return $pId;
			}
			else
			{
				return 0;
			}
		}
		
		public function personExists( $first_name, $last_name, $email )
		{		
			$checkPerson = $this->db->query('
												SELECT		p.person_id as person_id
												FROM		person p
												WHERE		p.last_name = ' . $this->db->escape($last_name) . '
												AND         p.first_name = ' . $this->db->escape($first_name) . '
												AND			p.email1 = ' . $this->db->escape($email) . '
										   ');
			
			if( $checkPerson->num_rows() > 0)
			{
				foreach ($checkPerson->result() as $personRow)
				{			
					$pId = $personRow->person_id;
					break;						
				}
				
				return $pId;
			}
			else
			{
				return 0;
			}
		}
		
		public function getPrimaryPersonSecurityKey( $urlKey )
		{		
			$checkPerson = $this->db->query('			
												SELECT    	p.person_id,
															IFNULL(p.password_md5,'.$this->db->escape('').') AS password_md5
												FROM      	person p
												INNER JOIN household_xref hxr ON hxr.person_id_fk=p.person_id
												INNER JOIN cmb_reg_history crh ON crh.household_id_fk=hxr.household_id_fk
												INNER JOIN cmb_center cc ON cc.cmb_center_id=crh.cmb_center_id_fk
												WHERE       crh.home_url = ' . $this->db->escape($urlKey) . '
												AND			hxr.member_type = ' . $this->db->escape('Primary') . '
												AND			crh.enabled = 1
												LIMIT		1;
												');
			
			if( $checkPerson->num_rows() > 0)
			{			
				return $checkPerson->row()->password_md5;
			}
			else
			{
				return "";
			}
		}

		public function getPrimaryPersonID( $urlKey )
		{		
			$checkPerson = $this->db->query('			
												SELECT    	p.person_id,
															IFNULL(p.password_md5,'.$this->db->escape('').') AS password_md5
												FROM      	person p
												INNER JOIN household_xref hxr ON hxr.person_id_fk=p.person_id
												INNER JOIN cmb_reg_history crh ON crh.household_id_fk=hxr.household_id_fk
												INNER JOIN cmb_center cc ON cc.cmb_center_id=crh.cmb_center_id_fk
												WHERE       crh.home_url = ' . $this->db->escape($urlKey) . '
												AND			hxr.member_type = ' . $this->db->escape('Primary') . '
												AND			crh.enabled = 1
												LIMIT		1;
												');
			
			if( $checkPerson->num_rows() > 0)
			{			
				return $checkPerson->row()->person_id;
			}
			else
			{
				return -1;
			}
		}

		public function getPersonSecurityKey( $personId )
		{		
			$checkPerson = $this->db->query('			
												SELECT    	p.person_id,
															IFNULL(p.password_md5,'.$this->db->escape('').') AS password_md5
												FROM      	person p
												WHERE       p.person_id = ' . $this->db->escape($personId) . '
												');
			
			if( $checkPerson->num_rows() > 0)
			{			
				return $checkPerson->row()->password_md5;
			}
			else
			{
				return "";
			}
		}

		//Get primary person_id based on any member of the household
		public function getHouseholdPrimaryPersonSecurityKey( $personId )
		{		
			$householdId = $this->getHouseholdID( $personId );
			if ($householdId < 1)
				return "";
			
			$checkPerson = $this->db->query('			
												SELECT    	p.person_id,
															IFNULL(p.password_md5,'.$this->db->escape('').') AS password_md5
												FROM      	person p
                        						INNER JOIN 	household_xref hxr ON hxr.person_id_fk = p.person_id
												WHERE       hxr.household_id_fk = ' . $this->db->escape($householdId) . '
                       							AND         hxr.member_type = ' . $this->db->escape("Primary") . '
											');
			
			if( $checkPerson->num_rows() > 0)
			{			
				return $checkPerson->row()->password_md5;
			}
			else
			{
				return "";
			}
		}

		public function personNameIsNull( $personId )
		{
			$checkIsNull = $this->db->query('
												SELECT		p.person_id
												FROM		person p
												WHERE		p.person_id = ' . $this->db->escape($personId) . '
												AND			p.person_name IS NULL
										   ');
		
			$isNull = FALSE;
			if( $checkIsNull->num_rows() == 1 )
			{
				$isNull = TRUE;
			}
			
			return $isNull;
		}
		
		
		public function getHouseholdID( $personId )
		{
			$checkForPersonId = $this->db->query('
													SELECT		hx.household_id_fk
													FROM		household_xref hx
													WHERE		hx.person_id_fk = ' . $personId . '
													AND			hx.enabled = 1
													LIMIT		1
											    ');
			
			$householdId = 0;
			if( $checkForPersonId->num_rows() == 1 )
			{
				$householdId = $checkForPersonId->row()->household_id_fk;
			}
			
			return $householdId;
		}		

		public function isPersonRole( $personId, $role )
		{
			$retval = $this->db->query('
													SELECT		pr.role_name
													FROM		person p
													INNER JOIN person_role_xref prxr ON prxr.person_id_fk=p.person_id
													INNER JOIN person_role pr ON pr.person_role_id=prxr.person_role_id_fk
													WHERE		p.person_id = ' . $personId . '
													GROUP BY pr.role_name;
											    ');
			
 			if( $retval->num_rows() > 0 )
			{
				foreach ($retval->result() as $resRow)
				{
					if ($resRow->role_name == $role)
						return TRUE;
				}
			}

			return FALSE;
		}

		public function getPersonRole( $personId )
		{
			$retval = $this->db->query('
													SELECT		pr.role_name
													FROM		person p
													INNER JOIN person_role_xref prxr ON prxr.person_id_fk=p.person_id
													INNER JOIN person_role pr ON pr.person_role_id=prxr.person_role_id_fk
													WHERE		p.person_id = ' . $personId . '
													GROUP BY pr.role_name;
											    ');
			
 			$roleArray = array();
			if( $retval->num_rows() > 0 )
			{
				foreach ($retval->result() as $resRow)
				{
					array_push($roleArray, $resRow->role_name);
				}
			}

			return $roleArray;
		}

		public function isPersonGroup( $personId, $group )
		{
			$retval = $this->db->query('
													SELECT		cg.group_name
													FROM		person p
													INNER JOIN person_role_xref prxr ON prxr.person_id_fk=p.person_id
													INNER JOIN person_role pr ON pr.person_role_id=prxr.person_role_id_fk
													INNER JOIN cmb_group cg ON cg.cmb_group_id=prxr.cmb_group_id_fk
													WHERE		p.person_id = ' . $personId . '
													GROUP BY cg.group_name;
											    ');
			
 			if( $retval->num_rows() > 0 )
			{
				foreach ($retval->result() as $resRow)
				{
					if ($resRow->group_name == $group)
						return TRUE;
				}
			}

			return FALSE;
		}

		public function isPersonGroupRole( $personId, $group, $role )
		{
			$retval = $this->db->query('
													SELECT		cg.group_name,
																pr.role_name
													FROM		person p
													INNER JOIN person_role_xref prxr ON prxr.person_id_fk=p.person_id
													INNER JOIN person_role pr ON pr.person_role_id=prxr.person_role_id_fk
													INNER JOIN cmb_group cg ON cg.cmb_group_id=prxr.cmb_group_id_fk
													WHERE		p.person_id = ' . $personId . '
											    ');
			
 			if( $retval->num_rows() > 0 )
			{
				foreach ($retval->result() as $resRow)
				{
					if (($resRow->group_name == $group) & ($resRow->role_name == $role))
						return TRUE;
				}
			}

			return FALSE;
		}

		public function getPersonGroup( $personId )
		{
			$retval = $this->db->query('
													SELECT		cg.group_name
													FROM		person p
													INNER JOIN person_role_xref prxr ON prxr.person_id_fk=p.person_id
													INNER JOIN person_role pr ON pr.person_role_id=prxr.person_role_id_fk
													INNER JOIN cmb_group cg ON cg.cmb_group_id=prxr.cmb_group_id_fk
													WHERE		p.person_id = ' . $personId . '
													GROUP BY cg.group_name;
											    ');
			
 			$groupArray = array();
			if( $retval->num_rows() > 0 )
			{
				foreach ($retval->result() as $resRow)
				{
					array_push($groupArray, $resRow->group_name);
				}
			}

			return $groupArray;
		}

		public function getPersonName( $personId )
		{
			$retval = $this->db->query('
													SELECT		IFNULL(p.first_name,' . $this->db->escape('') . ') AS first_name,
																IFNULL(p.last_name,' . $this->db->escape('') . ') AS last_name
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
 			if( $retval->num_rows() == 1 )
			{
				return ($retval->first_row()->first_name.' '.$retval->first_row()->last_name);
			}
			else
			{
				return "";				
			}
		}

		public function getPersonEmail( $personId )
		{
			$retval = $this->db->query('
													SELECT		IFNULL(p.email1,' . $this->db->escape('') . ') AS email
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
 			if( $retval->num_rows() == 1 )
			{
				return $retval->first_row()->email;
			}
			else
			{
				return "";				
			}			
		}
		
        public function getPersonUserName( $personId )
        {
            $retval = $this->db->query('
                                                    SELECT      IFNULL(p.person_name,' . $this->db->escape('') . ') AS username
                                                    FROM        person p
                                                    WHERE       p.person_id = ' . $personId . '
                                                ');
            
            if( $retval->num_rows() == 1 )
            {
                return $retval->first_row()->username;
            }
            else
            {
                return "";              
            }           
        }
        
		public function getPersonCredentials( $personId )
		{
			$retval = $this->db->query('
													SELECT		p.person_name,
																p.password
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
            $cred =  array();
			if( $retval->num_rows() == 1 )
			{
				array_push($cred,$retval->first_row()->person_name);
				array_push($cred,$retval->first_row()->password);
			}
			
			return $cred;
		}
		
		public function getPersonSecretPhrase( $personId )
		{
			$retval = $this->db->query('
													SELECT		IFNULL(p.secret_phrase,' . $this->db->escape('') . ') AS sp_value
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
 			if( $retval->num_rows() == 1 )
			{
				return $retval->first_row()->sp_value;
			}
			else
			{
				return "";				
			}			
		}

		public function updatePersonCredentials( $personId, $cred )
		{
			$md5password = md5($this->encrypt->decode($cred[1]));
			$retval = $this->db->query('
													UPDATE		person
													SET			person_name = ' . $this->db->escape($cred[0]) . ',
																password = ' . $this->db->escape($cred[1]) . ',
																password_nh = ' . $this->db->escape($this->encrypt->decode($cred[1])) . ',
																password_md5 = ' . $this->db->escape($md5password) . '
													WHERE		person_id = ' . $personId . '
											    ');
			
			return TRUE;
		}

		public function updatePersonPassword( $personId, $password )
		{
			$md5password = md5($this->encrypt->decode($password));
			$retval = $this->db->query('
													UPDATE		person
													SET			password = ' . $this->db->escape($password) . ',
																password_nh = ' . $this->db->escape($this->encrypt->decode($password)) . ',
																password_md5 = ' . $this->db->escape($md5password) . '												
													WHERE		person_id = ' . $personId . '
											    ');
			
			return TRUE;
		}

		//Checks if a person has credentials
		public function personHasCredentials( $personId )
		{
            $none = "none";
			$retval = $this->db->query('
													SELECT		IFNULL(p.person_name,' . $this->db->escape($none) . ') AS cred_value
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
            $hascred =  FALSE;
			if( $retval->num_rows() == 1 )
			{
				if($retval->first_row()->cred_value != "none")
				    $hascred = TRUE;
			}
			
			return $hascred;
		}

		//Checks if a person has secret phrase
		public function personHasSecretPhrase( $personId )
		{
            $none = "none";
			$retval = $this->db->query('
													SELECT		IFNULL(p.secret_phrase,' . $this->db->escape($none) . ') AS sp_value
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
            $hasphrase =  FALSE;
			if( $retval->num_rows() == 1 )
			{
				if($retval->first_row()->sp_value != "none")
				    $hasphrase = TRUE;
			}
			
			return $hasphrase;
		}

		//Create credentials for a person
		public function createPersonCredentials( $personId )
		{
			$retval = $this->db->query('
													SELECT		p.first_name,
																p.last_name
													FROM		person p
													WHERE		p.person_id = ' . $personId . '
											    ');
			
            $cred =  array();
			if( $retval->num_rows() == 1 )
			{
				
				$username = str_replace(" ","",$retval->first_row()->first_name) . '.' . str_replace(" ","",$retval->first_row()->last_name);
				//$username = str_replace(".","",$username);
				if ($username == "")
					$username = "unknown";
				if($this->personUserNameExists($username))
				{
					$username = $username . '1';
				}
				array_push($cred,$username);
				array_push($cred,$this->encrypt->encode($this->encrypt->get_random_password()));
			}
			
			return $cred;
		}

		public function isHouseholdRegistered( $householdId )
		{
			$checkHouseholdReg = $this->db->query('
													SELECT		crh.cwr_reg_history_id
													FROM		cwr_reg_history crh
													JOIN		household_xref hxr
													ON			hxr.person_id_fk = crh.person_id_fk
													WHERE 		hxr.household_id_fk = ' . $this->db->escape($householdId) . ' 
											    ');
			
			$householdIsRegistered = FALSE;
			if( $checkHouseholdReg->num_rows() > 0 )
			{
				$householdIsRegistered = TRUE;
			}
			
			return $householdIsRegistered;
		}
		
		
		public function personUserNameExists( $username )
		{
			$checkUNExists = $this->db->query('
													SELECT		p.person_id
													FROM		person p
													WHERE 		p.person_name = ' . $this->db->escape($username) . ' 
											    ');
			
			$unExists = FALSE;
			if( $checkUNExists->num_rows() > 0 )
			{
				$unExists = TRUE;
			}
			
			return $unExists;
		}

		private function getCleanAddress( $address )
		{
			$explodedAddress = explode(" ", $address);
			
			$nonBlankCounter = 0;
			$addressString = "";
			for( $i = 0; $i < sizeof($explodedAddress); $i++ )
			{
				if( trim($explodedAddress[$i]) != "" )
				{
					if($addressString == "")
					{
						$addressString = trim($explodedAddress[$i]);
					}
					else
					{
						$addressString .= " " . trim($explodedAddress[$i]);
					}
					$nonBlankCounter++;
					
					if($nonBlankCounter == 2)
					{
						break;
					} 
				}
			}
			
			return $addressString;
		}
				
        //Create an entry for forgot_password
        public function createForgotPasswordEntry( $personID )
        {
            $this->load->library('encrypt');
            $this->load->helper('string');
            
            $curdate = date('Y-m-d H:i:s', time());

            //Check if there is already an active entry for specified person_id
            $retval = $this->db->query('
                                        SELECT      fp.person_id_fk
                                        FROM        forgot_password fp
                                        WHERE       fp.person_id_fk = ' . $this->db->escape($personID) . '
                                        AND         STR_TO_DATE(' . $this->db->escape($curdate)  . ',' . $this->db->escape("%Y-%m-%d %T")  . ') <= DATE_ADD(fp.date_created, INTERVAL 1 DAY)
                                        AND         fp.enabled = 1
                                    ');
            
            if( $retval->num_rows() > 0 )
            {
                return "";
            }

            $urlKey = random_string("alnum",64);
                        
            //Insert the person_group object
            $this->db->query('
                                INSERT INTO         forgot_password
                                                    (
                                                        person_id_fk,
                                                        url_key,
                                                        date_created,
                                                        date_modified,
                                                        enabled
                                                    )
                                VALUES              (
                                                        ' . $this->db->escape($personID)  . ',
                                                        ' . $this->db->escape($urlKey)  . ',
                                                        STR_TO_DATE(' . $this->db->escape($curdate)  . ',' . $this->db->escape("%Y-%m-%d %T")  . '),
                                                        STR_TO_DATE(' . $this->db->escape($curdate)  . ',' . $this->db->escape("%Y-%m-%d %T")  . '),
                                                        1                                                       
                                                    )
                             ');
                                        
            return $urlKey;
        }

        public function getPersonIDFromForgotPasswordKey( $urlKey )
        {
            $retval = $this->db->query('
                                                    SELECT      fp.person_id_fk
                                                    FROM        forgot_password fp
                                                    WHERE       fp.url_key = ' . $this->db->escape($urlKey) . '
                                                    AND         fp.enabled = 1
                                                ');
            
            if( $retval->num_rows() > 0 )
            {
                return $retval->row()->person_id_fk;
            }
            else
                return 0;
        }

        //Check if password key has expired
        public function isPasswordKeyExpired( $urlKey )
        {
            $curdate = new DateTime(date('Y-m-d H:i:s', time()));
            
            //Find password key create date
            $tmp = $this->db->query('           
                                                SELECT      fp.date_created     
                                                FROM        forgot_password fp
                                                WHERE       fp.url_key = ' . $this->db->escape($urlKey) . '
                                                AND         fp.enabled = 1
                                            ');
            
            if( $tmp->num_rows() > 0)
            {           
                $date_created = new DateTime($tmp->row()->date_created);
                $expire_date = new DateTime($tmp->row()->date_created);
                $expire_date->modify("+1 day");
                
                //echo "Create Date: ".$date_created->format('Y-m-d H:i:s')."</br>";
                //echo "Expire Date: ".$expire_date->format('Y-m-d H:i:s')."</br>";
                //echo "Currnt Date: ".$curdate->format('Y-m-d H:i:s')."</br>";
                //die;
            }
            else
                return TRUE;

            if ($curdate <= $expire_date)
            {
                //echo "Password Key Valid"."</br>".
                //die;
                return FALSE;
            }
            else
            {
                //echo "Password Key Expired"."</br>".
                //die;
                //Expired
                $this->db->query('
                                    UPDATE          forgot_password
                                    SET             enabled  = 0
                                    WHERE           url_key = ' . $this->db->escape($urlKey) . '
                                 ');
                                 
                return TRUE;
            }
        }

        //Reset password
        public function resetPersonPassword( $personID, $urlKey, $newPassword )
        {
            //Update password and remove initial_password
            $this->updatePersonAccountPassword( $personID, $newPassword );

            //Disable forgot_password entry
            $this->db->query('
                                UPDATE          forgot_password
                                SET             enabled  = 0
                                WHERE           person_id_fk = ' . $this->db->escape($personID) . '
                                AND             url_key = ' . $this->db->escape($urlKey) . '
                             ');
        }

        //Reset password
        public function updatePersonAccountPassword( $personID, $newPassword )
        {
            $this->load->library('encrypt');
            
            //Update password and remove initial_password
            $curdate = date('Y-m-d H:i:s', time());
            $this->db->query('
                                UPDATE          person
                                SET             password = ' . $this->db->escape($this->encrypt->encode($newPassword)) . ',
                                                password_nh = ' . $this->db->escape($newPassword) . ',
                                                date_modified = STR_TO_DATE(' . $this->db->escape($curdate)  . ',' . $this->db->escape("%Y-%m-%d %T")  . ')
                                WHERE           person_id = ' . $this->db->escape($personID) . '
                             ');
        }

		public function sendPersonRegistrationEmail( $personValue )
		{
			$curyear = $this->getCurrentYear();
			
			$personValue = Person_value::cast($personValue);
			
			$this->load->library('encrypt');
			
			$message = '';
			$message .= '<p><b>You have Sponsored a Walk/Run for CORD '.$curyear.' Participant</b></p>';
			$message .= '<p>Dear ' . $personValue->getFirstName() . ',</p>';
			$message .= '<p>Your registration for sponsoring a Walk/Run for CORD '.$curyear.' participant has been confirmed:</p>';
			$message .= '<p>';
			$message .= '<table border="0">';
			$message .= '<tr><td width="150">Username:</td><td>' . $personValue->getPersonName() . '</td></tr>';
			$message .= '<tr><td width="150">Password:</td><td>' . $this->encrypt->decode($personValue->getPassword()) . '</td></tr>';
			$message .= '<tr><td width="150">Name:</td><td>' . $personValue->getFirstName() . ' ' . $personValue->getLastName() . '</td></tr>';
			$message .= '<tr><td width="150">Address:</td><td>' . $personValue->getAddress1() . '</td></tr>';
			$message .= '<tr><td width="150">City:</td><td>' . $personValue->getCity() . '</td></tr>';
			$message .= '<tr><td width="150">State:</td><td>' . $personValue->getState() . '</td></tr>';
			$message .= '<tr><td width="150">Zip:</td><td>' . $personValue->getZip() . '</td></tr>';
			$message .= '</table>';
			$message .= '</p>';
			$message .= '<p>The Chinmaya Mission Boston Community Service Team would like to thank you for supporting the non-profit CORD organization in helping the poor transform their lives through self-driven programs.</p>';
			$message .= '<p>Sincerely,</p>';
			$message .= '<p>Community Services Team<br />Chinmaya Mission Boston</p>';			
			
			$this->load->library('email');
			
			$config['protocol'] = 'smtp';
			$config['smtp_host'] = $this->config->item('email_smtp_host');
			$config['smtp_user'] = $this->config->item('email_smtp_user');
			$config['smtp_pass'] = $this->config->item('email_smtp_pass');
			$config['smtp_port'] = $this->config->item('email_smtp_port');
			$config['mailtype'] = 'html';
	
			$this->email->initialize($config);
	
			$this->email->from($this->config->item('email_smtp_user'), $this->config->item('email_smtp_user_name'));
			$this->email->to( $personValue->getEmail1() );
			$this->email->cc('');
			$this->email->bcc('');
			
			$this->email->subject('Registration confirmation for sponsoring a Walk/Run for CORD '.$curyear.' participant');
			$this->email->message($message);
			
			$this->email->send();
		}

        //Email reset password link
        public function sendPersonResetPasswordEmail( $email, $username, $urlKey )
        {
            $message = '';
            $message .= '<p>To reset your password for,</p>';
            $message .= '<p>User Name: '.$username.'</p>';
            $message .= '<p>Go to: <a href="'.base_url().'cmb/member/reset_password_from_email/'.$urlKey.'">Reset Password</a></p>';
            $message .= '<p>If you have any questions, please contact us at: cmbproject@gmail.com</p>';

            $this->load->library('email');
            $this->email->set_newline("\r\n");
            
            $config['protocol'] = $this->config->item('email_smtp_protocol');
            $config['smtp_host'] = $this->config->item('email_smtp_host');
            $config['smtp_user'] = $this->config->item('email_smtp_user');
            $config['smtp_pass'] = $this->config->item('email_smtp_pass');
            $config['smtp_port'] = $this->config->item('email_smtp_port');
            $config['mailtype'] = $this->config->item('email_smtp_mailtype');
    
            $this->email->initialize($config);
            $this->email->from($this->config->item('email_smtp_user'), $this->config->item('email_smtp_user_name'));
            
             $this->email->to( $email );
            
            $this->email->cc('');
            $this->email->bcc('cmbproject@gmail.com');
            
            $this->email->subject('Reset Password Request');
            $this->email->message($message);
            
            $this->email->send();
        }		
	}
?>